package helloworld.dao.msaccess;

import helloworld.admin.bean.CourseAdminBean;
import helloworld.dao.CourseDao;
import helloworld.dao.DAOException;
import helloworld.exception.DuplicateCourseException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.util.List;
import java.util.ArrayList;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import helloworld.exception.ErrorLevel;

import java.util.Properties;
import java.util.ResourceBundle;
import java.util.Enumeration;
import helloworld.common.DbSQLCommon;

/**
 * CourseDAO encapsulates data access and manipulation It represents the Data
 * Access Object (DAO) Pattern. A DAO abstract the mechanisms of accessing and
 * manipulating a persistence store such as database,
 * 
 * using SQLParameter-like processor
 * 
 * @author TUYEN-DM
 * @version $Revision: $ $Date: 2009/05/09 $
 */
public class CourseDAO_AccessImpl implements CourseDao {

	private DbSQLCommon clsDbSQLCommon;

	protected static String SELECT_SQL = "";
	protected static String SEARCH_SQL = "";
	protected static String INSERT_SQL = "";
	protected static String UPDATE_SQL = "";
	protected static String DELETE_SQL = "";
	protected static String DELETE_ID_SQL = "";

	/**
	 * Constructor
	 */
	public CourseDAO_AccessImpl() {
		clsDbSQLCommon = new DbSQLCommon();
	}

	/**
	 * Search course by course name
	 * 
	 * @param emailAddress
	 * @return
	 * @throws DAOException
	 */
	public CourseAdminBean fetchDetail(String courseName) throws DAOException {

		PreparedStatement pSmnt = null;

		Connection conn = getMySQLConnnection();
		CourseAdminBean course = null;

		try {
			SELECT_SQL = clsDbSQLCommon.fncGetResourceSQL("db-sql",
					"course.select");
			pSmnt = conn.prepareStatement(SELECT_SQL);
			pSmnt.setString(1, courseName);// set parameter 1

			ResultSet set = pSmnt.executeQuery();
			if (set.next()) {
				course = extractCourseDetailFromResultSet(set);
			}
		} catch (SQLException se) {
			// TODO: handle exception
			String[] valueReplacementArray = { courseName };
			throw new DAOException(se, "error.database.fetch.coursedetail",
					valueReplacementArray, ErrorLevel.ERROR);
		} finally {
			try {
				conn.close();
			} catch (SQLException sq) {
			}
		}

		return course;
	}

	/**
	 * Search course by courseID 2009/05/09
	 * 
	 * @param courseID
	 * @return
	 * @throws DAOException
	 */
	public CourseAdminBean fetchDetailID(int courseID) throws DAOException {

		PreparedStatement pSmnt = null;

		Connection conn = getMySQLConnnection();
		CourseAdminBean course = null;

		try {
			SELECT_SQL = clsDbSQLCommon.fncGetResourceSQL("db-sql",
					"course.selectID");
			pSmnt = conn.prepareStatement(SELECT_SQL);
			pSmnt.setInt(1, courseID);// set parameter 1

			ResultSet set = pSmnt.executeQuery();
			if (set.next()) {
				course = extractCourseDetailFromResultSet(set);
			}
		} catch (SQLException se) {
			// TODO: handle exception
			String[] valueReplacementArray = { courseID + "" };
			throw new DAOException(se, "error.database.fetch.coursedetail",
					valueReplacementArray, ErrorLevel.ERROR);
		} finally {
			try {
				conn.close();
			} catch (SQLException sq) {
			}
		}

		return course;
	}

	/**
	 * 
	 * @author Tuyen-DM
	 * @param courseName
	 * @return
	 * @throws DAOException
	 */
	public CourseAdminBean[] find(String courseName) throws DAOException {
		PreparedStatement pSmnt = null;
		// Connection conn = getConnection();
		Connection conn = getMySQLConnnection();
		CourseAdminBean course = null;
		List list = new ArrayList();

		try {
			SEARCH_SQL = clsDbSQLCommon.fncGetResourceSQL("db-sql",
					"course.select");
			pSmnt = conn.prepareStatement(SEARCH_SQL);
			pSmnt.setString(1, "%" + courseName + "%");// search LIKE

			ResultSet set = pSmnt.executeQuery();
			while (set.next()) {
				course = extractCourseDetailFromResultSet(set);
				list.add(course);
			}
		} catch (SQLException se) {
			// TODO: handle exception
			String[] valueReplacementArray = { courseName };
			throw new DAOException(se, "error.database.fetch.coursedetail",
					valueReplacementArray, ErrorLevel.ERROR);
		} finally {
			try {
				conn.close();
			} catch (SQLException sq) {
			}
		}

		CourseAdminBean[] courses = null;
		if (list.size() > 0) {
			courses = (CourseAdminBean[]) list.toArray(new CourseAdminBean[list
					.size()]);
		}

		return courses;
	}

	/**
	 * insert new course into database
	 * 
	 * @param course
	 * @return
	 * @throws DAOException
	 * @throws DuplicateCourseException
	 */
	public int create(CourseAdminBean course) throws DAOException,
			DuplicateCourseException {
		int intInsertedRows = 0;
		PreparedStatement pStmt = null;
		// Connection conn = getConnection();
		Connection conn = getMySQLConnnection();

		try {
			CourseAdminBean det = fetchDetail(course.getCourseName());
			if (det != null) {
				intInsertedRows = -1;
				throw new DuplicateCourseException(
						"error.database.course.duplicate", course
								.getCourseName(), null, ErrorLevel.WARNING);
			} else {
				INSERT_SQL = clsDbSQLCommon.fncGetResourceSQL("db-sql",
						"course.insert");
				pStmt = conn.prepareStatement(INSERT_SQL);
				pStmt.setString(1, course.getCourseName());
				pStmt.setString(2, course.getContent());
				pStmt.setString(3, course.getOpenDate());
				pStmt.setString(4, course.getCloseDate());
				pStmt.setInt(5, course.getClasses());
				pStmt.setString(6, course.getFee());
				pStmt.setString(7, course.getQualification());
				pStmt.setString(8, course.getRecruitTime());

				intInsertedRows = pStmt.executeUpdate();
			}

		} catch (SQLException se) {
			// TODO: handle exception
			String[] valueReplacementArray = { course.getCourseName() };
			throw new DAOException(se, "error.database.course.save",
					valueReplacementArray, ErrorLevel.ERROR);
		} finally {
			try {
				conn.close();
			} catch (SQLException sq) {
			}
		}

		return intInsertedRows;
	}

	/**
	 * update course information
	 * 
	 * @param course
	 * @return
	 * @throws DAOException
	 * @throws DuplicateCourseException
	 */
	public int update(CourseAdminBean course) throws DAOException,
			DuplicateCourseException {
		int intUpdatedRows = 0;
		PreparedStatement pStmt = null;
		// Connection conn = getConnection();
		Connection conn = getMySQLConnnection();

		try {
			CourseAdminBean det = fetchDetailID(course.getCourseID());

			if (det != null
					&& !det.getCourseName().equals(course.getCourseName())) {
				CourseAdminBean det2 = fetchDetail(course.getCourseName());
				if (det2 != null
						&& det2.getCourseName().equals(course.getCourseName())) {
					throw new DuplicateCourseException(
							"error.database.course.duplicate", course
									.getCourseName(), null, ErrorLevel.WARNING);
				}
			}

			UPDATE_SQL = clsDbSQLCommon.fncGetResourceSQL("db-sql",
					"course.update");
			pStmt = conn.prepareStatement(UPDATE_SQL);
			pStmt.setString(1, course.getCourseName());
			pStmt.setString(2, course.getContent());
			pStmt.setString(3, course.getOpenDate());
			pStmt.setString(4, course.getCloseDate());
			pStmt.setInt(5, course.getClasses());
			pStmt.setString(6, course.getFee());
			pStmt.setString(7, course.getQualification());
			pStmt.setString(8, course.getRecruitTime());
			pStmt.setInt(9, course.getCourseID());

			intUpdatedRows = pStmt.executeUpdate();

		} catch (SQLException se) {
			String[] valueReplacementArray = { course.getCourseName() };
			throw new DAOException(se, "error.database.course.save",
					valueReplacementArray, ErrorLevel.ERROR);
		} finally {
			try {
				conn.close();
			} catch (SQLException sq) {
			}
		}
		return intUpdatedRows;
	}

	/**
	 * delete course
	 * 
	 * @param emailAddress
	 * @throws DAOException
	 */
	public void delete(int id) throws DAOException {
		PreparedStatement pSmnt = null;
		// Connection conn = getConnection();
		Connection conn = getMySQLConnnection();

		try {
			DELETE_ID_SQL = clsDbSQLCommon.fncGetResourceSQL("db-sql",
					"course.deleteID");
			pSmnt = conn.prepareStatement(DELETE_ID_SQL);
			pSmnt.setInt(1, id);
			pSmnt.executeUpdate();
		} catch (SQLException se) {
			// TODO: handle exception
			String[] valueReplacementArray = { new Integer(id).toString() };
			throw new DAOException(se, "error.database.course.delete",
					valueReplacementArray, ErrorLevel.ERROR);
		} finally {
			try {
				conn.close();
			} catch (SQLException sq) {
			}
		}
	}

	/**
	 * @param rs
	 *            <p>
	 *            Set results to data object: course
	 *            </p>
	 * 
	 */
	protected CourseAdminBean extractCourseDetailFromResultSet(ResultSet rs)
			throws SQLException {
		CourseAdminBean course = new CourseAdminBean();
		course.setCourseID(rs.getInt("CourseID"));
		course.setCourseName(rs.getString("CourseName"));
		course.setContent(rs.getString("Content"));
		course.setOpenDate(rs.getString("OpenDate"));
		course.setCloseDate(rs.getString("CloseDate"));
		course.setClasses(rs.getInt("Classes"));
		course.setFee(rs.getString("Fee"));
		course.setQualification(rs.getString("Qualification"));
		course.setRecruitTime(rs.getString("RecruitTime"));

		return course;
	}

	/**
	 * database connection processor
	 */
	protected Connection getConnection() throws DAOException {
		Connection dbConnection = null;
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			dbConnection = DriverManager
					.getConnection("jdbc:odbc:helloworld_TRAINING");

		} catch (ClassNotFoundException cfe) {
			throw new DAOException(cfe, "error.database.config", null,
					ErrorLevel.FATAL);
		} catch (SQLException se) {
			throw new DAOException(se, "error.database.config", null,
					ErrorLevel.FATAL);
		}
		return dbConnection;
	}

	/**
	 * @author Tuyen-DM
	 *         <p>
	 *         Mysql connection test
	 *         </p>
	 * @return
	 * @throws DAOException
	 */
	protected Connection getMySQLConnnection() throws DAOException {
		Connection dbConnection = null;
		try {
			// Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			// dbConnection =
			// DriverManager.getConnection("jdbc:odbc:helloworld_TRAINING");
			// get database properties test
			Properties prop = new Properties();
			ResourceBundle resource = ResourceBundle.getBundle("db-config");
			Enumeration enumTemp = resource.getKeys();
			while (enumTemp.hasMoreElements()) {
				String key = (String) enumTemp.nextElement();
				prop.put(key, resource.getString(key));
			}

			String userName = prop.getProperty("username");
			String password = prop.getProperty("userpassword");
			String url = prop.getProperty("database_url");
			Class.forName(prop.getProperty("database_Server"));
			dbConnection = DriverManager.getConnection(url, userName, password);
			System.out.println("Database connection established");

		} catch (ClassNotFoundException cfe) {
			throw new DAOException(cfe, "error.database.config", null,
					ErrorLevel.FATAL);
		} catch (SQLException se) {
			throw new DAOException(se, "error.database.config", null,
					ErrorLevel.FATAL);
		}
		return dbConnection;
	}

	/**
	 * debug test
	 * 
	 * @param args
	 */
	public static void main(String[] args) {
		try {
			CourseDAO_AccessImpl dao = new CourseDAO_AccessImpl();
			CourseAdminBean course = dao.fetchDetail("1");
			System.out.println(course);
		} catch (DAOException de) {
			// TODO: handle exception
			Log log = LogFactory.getLog("CourseDAO");
			de.log(log);
		}
	}
}
